Transpose column from one table to row headers in another and populate with data from original table

Hi,

Similar to one of my previous posts, I am trying to extract data from a column based on a "text" value when a match is found in another. The the example shown below, I am attempting to populate Table 2 with the Design ID (e.g. DE-2241) against the respective suppliers (i.e. Company 2, 3, 4, 7 and 11). Table 1 contains the original data and I want to copy the "Design ID" into the respective intersection in table 2 (i.e. where the RE link matches the respective supplier) - examples of this are shown with the "Green", "Orange" and "Red" shaded cells.

I have attempted to use a similar approach used in a response to one of my previous posts - see below

At first, I thought that it has worked (see values in the red boxes of Table 1 and Table 2), but then realised that the value returned was not DE-33 as expected. Instead, the value returned was DE-2241. I am assuming that the syntax is not correct and simply retuned the last value it found in the table.

Is the syntax correct?

Is it possible to use the syntax in this way?

Is there a better way to do this (Transpose column d and then populate with data as shown)?